package onecontact;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DatabaseUtils {
	private static Connection ConnectOracle(String computerName, String port,
			String sID, String userName, String password) {
		System.out.println("-------- Oracle JDBC Connection Testing ------");

		try {

			Class.forName("oracle.jdbc.driver.OracleDriver");

		} catch (ClassNotFoundException e) {

			System.out.println("Where is your Oracle JDBC Driver?");
			e.printStackTrace();
			return null;

		}

		System.out.println("Oracle JDBC Driver Registered!");

		Connection connection = null;

		try {

			connection = DriverManager
					.getConnection("jdbc:oracle:thin:@" + computerName + ":"
							+ port + ":" + sID, userName, password);

		} catch (SQLException e) {

			System.out.println("Connection Failed! Check output console");
			e.printStackTrace();
			return null;

		}

		if (connection != null) {
			System.out.println("You made it, take control your database now!");
			return connection;
		} else {
			System.out.println("Failed to make connection!");
			return null;
		}
	}

	private static void createTblTag(Connection conn) throws SQLException {
		String sqlString2 = "CREATE TABLE tag ( "
				  +"id numeric NOT NULL , "
				  +"name NVARCHAR2(1000) NOT NULL, "  
				  +"enable SMALLINT NOT NULL , "
				  +"last_update DATE NOT NULL, "
				  +"PRIMARY KEY  (id) "
				  +") ";
		String sqlString3 ="CREATE SEQUENCE tag_sequence ";
				
		String sqlString4 = "CREATE OR REPLACE TRIGGER tag_before_trigger " 
				+"BEFORE INSERT ON tag FOR EACH ROW  "
				+"BEGIN "
				+"IF (:NEW.id IS NULL) THEN "
				+"SELECT tag_sequence.nextval INTO :NEW.id "
				+"FROM DUAL; "
				+"END IF; "
				+"END; ";
		try {
			Statement st = conn.createStatement();
			System.out.println("create table tag");
			st.execute(sqlString2);
			System.out.println("create sequence tag");
			st.execute(sqlString3);
			System.out.println("create trigger tag");
			st.execute(sqlString4);
		} catch (SQLException e) {
			throw e;
		}
	}
	
	private static void createTblNews(Connection conn) throws SQLException {
		String sqlString2 = "CREATE TABLE news ( "
				  +"id numeric NOT NULL , "
				  +"title VARCHAR(255) NOT NULL, "
				  +"intro CLOB DEFAULT NULL, "
				  +"thumbnail VARCHAR(100), "
				  +"content CLOB DEFAULT NULL, "
				  +"category SMALLINT NOT NULL , "
				  +"tags NVARCHAR2(1000), "
				  +"enable SMALLINT NOT NULL , "
				  +"last_update DATE NOT NULL, "
				  +"PRIMARY KEY  (id) "
				  +") ";
		String sqlString3 ="CREATE SEQUENCE news_sequence ";
				
		String sqlString4 = "CREATE OR REPLACE TRIGGER news_before_trigger " 
				+"BEFORE INSERT ON news FOR EACH ROW  "
				+"BEGIN "
				+"IF (:NEW.id IS NULL) THEN "
				+"SELECT news_sequence.nextval INTO :NEW.id "
				+"FROM DUAL; "
				+"END IF; "
				+"END; ";
		try {
			Statement st = conn.createStatement();
			System.out.println("create table news");
			st.execute(sqlString2);
			System.out.println("create sequence news");
			st.execute(sqlString3);
			System.out.println("create trigger news");
			st.execute(sqlString4);
		} catch (SQLException e) {
			throw e;
		}
	}
	
	private static void createTblAccount(Connection conn) throws SQLException {
		String sqlString2 = "CREATE TABLE account ( "
				  +"id numeric NOT NULL , "
				  +"username VARCHAR(45) NOT NULL, "
				  +"password VARCHAR(100) NOT NULL, "
				  +"role VARCHAR(45) NOT NULL,   "
				  +"enable SMALLINT NOT NULL , "
				  +"date_create DATE NOT NULL, "
				  +"PRIMARY KEY  (id) "
				  +") ";
		String sqlString3 ="CREATE SEQUENCE account_sequence ";
				
		String sqlString4 = "CREATE OR REPLACE TRIGGER account_before_trigger " 
				+"BEFORE INSERT ON account FOR EACH ROW  "
				+"BEGIN "
				+"IF (:NEW.id IS NULL) THEN "
				+"SELECT account_sequence.nextval INTO :NEW.id "
				+"FROM DUAL; "
				+"END IF; "
				+"END; ";
		String sqlString5="INSERT INTO account(username,password,role,enable,date_create) "
				+"VALUES('admin','-2015','ADMIN','1',TO_DATE('2015-01-01','YYYY-MM-DD'))";
		try {
			Statement st = conn.createStatement();
			System.out.println("create table account");
			st.execute(sqlString2);
			System.out.println("create sequence account");
			st.execute(sqlString3);
			System.out.println("create trigger account");
			st.execute(sqlString4);
			System.out.println("insert account admin");
			st.execute(sqlString5);
		} catch (SQLException e) {
			throw e;
		}
	}

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		Connection conn = ConnectOracle("118.70.184.6", "1521", "ora11g",
				"vasoa", "vasoa");
		if (conn != null) {

			try {				
				createTblNews(conn);
				createTblAccount(conn);
				createTblTag(conn);
//				String sql = "CREATE OR REPLACE TRIGGER tag_before_trigger "
//				+"BEFORE INSERT ON tag FOR EACH ROW " 
//				+"BEGIN "
//				  +"IF (:NEW.id IS NULL) THEN "
//				    +"SELECT tag_sequence.nextval INTO :NEW.id "
//				    +"FROM DUAL; "
//				  +"END IF; "
//				+"END; ";
//				
////				sql = "drop TRIGGER tag_before_trigger";
//				try {
//					Statement st = conn.createStatement();
//					st.execute(sql);
//				} catch (SQLException e) {
//					throw e;
//				}
				
			} catch (SQLException e1) {
				e1.printStackTrace();
			} finally {
				try {
					if (conn != null)
						conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
}
